﻿// --------------------------------------------------------------------
// <summary>
// Script table creator classes
// </summary>
// --------------------------------------------------------------------
namespace ReportCaching
{
    using System;
    using System.Data;
    using System.Data.SqlTypes;
    using System.Text;
    using System.Data.SqlClient;

    #region column properties

    /*
    ColumnName = AddressID
    ColumnSize = 4
    NumericPrecision = 10
    NumericScale = 255
    BaseColumnName = AddressID
    AllowDBNull = False
    DataTypeName = int
    XmlSchemaCollectionDatabase = 
    XmlSchemaCollectionOwningSchema = 
    XmlSchemaCollectionName = 
    UdtAssemblyQualifiedName = 
    NonVersionedProviderType = 8
   * */

    #endregion

    /// <summary>
    /// Helper class for create new table
    /// </summary>
    public static class TableCreator
    {
        #region Script table
        /// <summary>
        /// Create table script
        /// </summary>
        /// <param name="dataReader">extract data from datareader</param>
        /// <param name="databaseName">destination database name</param>
        /// <param name="tableName">destination table name </param>
        /// <param name="additionalFields">additional fields for new table</param>
        /// <returns>returns new TableScript class</returns>
        public static TableScript ScriptTable(IDataReader dataReader,SqlString databaseName, SqlString tableName, SqlString additionalFields)
        {
            DataTable dataTable = dataReader.GetSchemaTable();

            TableScript tableScript = ScriptTable(dataTable,databaseName, tableName, additionalFields);

            if (dataTable != null) dataTable.Dispose();

            return tableScript;
        }
        /// <summary>
        /// Script tabla
        /// </summary>
        /// <param name="dataTable">schema data table</param>
        /// <param name="databaseName">destination database name</param>
        /// <param name="tableName">destination table name</param>
        /// <param name="additionalFields">additional fields for new table</param>
        /// <returns>returns new TableScript class</returns>
        private static TableScript ScriptTable(DataTable dataTable, SqlString databaseName,SqlString tableName, SqlString additionalFields)
        {

            StringBuilder table = new StringBuilder(String.Empty);
            string tempTable;
            StringBuilder insertScript= new StringBuilder(String.Empty);
            StringBuilder valueScript = new StringBuilder(String.Empty);
            SqlCommand sqlCommandForInsert = new SqlCommand();
            

            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    // only visible columns
                    if (!bool.Parse(row["IsHidden"].ToString()))
                    {
                        table.AppendFormat(",[{0}] {1}", row["ColumnName"], FieldDataTypeFormat(row));
                        table.AppendLine(FieldNullFormat(row));
                        // insert script for newsted exec insert
                        insertScript.AppendFormat(",[{0}]", row["ColumnName"]);
                        valueScript.AppendFormat(",@{0}", row["ColumnName"]);
                        //sqlCommandForInsert.Parameters.Add(row["ColumnName"],);
                        //sqlCommandForInsert.Parameters[0].
                    }
                }
            }
            

            tempTable = table.ToString();
            if (!string.IsNullOrEmpty((string)additionalFields)) 
                {
                    table.AppendFormat(",{0}", additionalFields);
                }

            TableScript tableScript = new TableScript();
            tableScript.Table = string.Format("use {0};Create table {1} ( \r\n{2}\r\n)",databaseName,tableName, table.ToString().Substring(1));
            tableScript.TempTable =
                string.Format("declare {0} table ( \r\n{1}\r\n)", Constants.TempTableName, tempTable.Substring(1));
            tableScript.InsertScrip = String.Format("INSERT INTO {0} ({1}) values({2});",tableName,insertScript.ToString().Substring(1),valueScript.ToString().Substring(1));

            return tableScript;
        }


        /// <summary>
        /// Null formatting
        /// </summary>
        /// <param name="row">datarow for create nullable prop. (row["AllowDBNull"] = true)</param>
        /// <returns>NOT NULL or NULL</returns>
        private static string FieldNullFormat(DataRow row)
        {
            /*string s;
      if (row["AllowDBNull"].ToString() == "true")
        s = "NULL";
      else s = "NOT NULL";

      if (s.Length != 0) s += " ";*/
            return "NULL ";
        }

        /// <summary>
        /// Field datatype format
        /// </summary>
        /// <param name="row">datarow for field type</param>
        /// <returns>datatype name for field</returns>
        private static string FieldDataTypeFormat(DataRow row)
        {
            StringBuilder s = new StringBuilder(String.Format("{0}", row["DataTypeName"]));

            s.Append(FieldLengthFormat(row));
            s.Append(FieldPrecisionScaleFormat(row));

            return s.ToString();
        }

        /// <summary>
        /// Precision/Scale format if any
        /// </summary>
        /// <param name="row">precision and scale</param>
        /// <returns>precision, scale, lenght for field</returns>
        private static string FieldPrecisionScaleFormat(DataRow row)
        {
            string s = String.Empty;
            if (row["DataTypeName"].ToString().IndexOf("decimal") != -1
                || row["DataTypeName"].ToString().IndexOf("numeric") != -1
                )
                {
                s = String.Format("({0}{1})", row["NumericPrecision"],
                                  ((row["AllowDBNull"].ToString() == "true")
                                       ? (String.Empty)
                                       : ("," + row["NumericScale"])));
            }
            if (s.Length != 0)
            {
                s += " ";
            }
            return s;
        }

        /// <summary>
        /// Field length format if any
        /// </summary>
        /// <param name="row">one field properties</param>
        /// <returns>formatted lenght</returns>
        private static string FieldLengthFormat(DataRow row)
        {
            string s = String.Empty;

            if (row["DataTypeName"].ToString().IndexOf("char") != -1
                || row["DataTypeName"].ToString().IndexOf("binary") != -1)
            {
                s =
                    String.Format(" ({0})",
                                  (row["ColumnSize"].ToString() == "0" ? "varcharmax" : row["ColumnSize"].ToString()));
            }

            s += " ";
            return s;
        }

        #endregion
    }   
}